﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using Cinch;
using System.ComponentModel;
using System.Reflection;
using SQLServerCompanion.HelperClasses;

namespace SQLServerCompanion.HelperClasses
{
    public class DBServerConnectionSingleton : INotifyPropertyChanged
    {
        private static DBServerConnectionSingleton _instance;
        private static object syncLock = new object(); // Lock synchronization object

        private string _userName = "";
        private string _password = "";
        private bool _useIntegratedAuthentication = false;
        private string _sqlServerName = null;

        private Server _sqlServer;
        private ServerConnection _svrConnection;
        private SqlConnectionInfo _cn = new SqlConnectionInfo();

        Scripter _scripter = null;

        #region Constructor

        private DBServerConnectionSingleton() { }

        #endregion

        #region Methods
        /// <summary>
        /// Initialises the singleton
        /// </summary>
        public void Initialise()
        {
            // does nothing as all the setup is done on first instantiation, this is just a dummy method as something to call to create the instance
        }

        public Server ConnectToDB(string serverName, bool useIntegratedAuthentication, string username, string password)
        {
            try
            {
                _userName = username;
                _password = password;
                _sqlServerName = serverName;
                _useIntegratedAuthentication = useIntegratedAuthentication;
                ConnectToSQLServer();
                return _sqlServer;
            }
            catch
            {
                // We couldn't connect to db server
                return null;
            }
        }

        public bool ConnectToSQLServer()
        {
            try
            {
                                
                _cn.ConnectionProtocol = _selectedNetworkProtocol;
                
                
                if (!_useIntegratedAuthentication)
                {
                    _svrConnection = new ServerConnection(_sqlServerName);
                    _svrConnection.LoginSecure = false;
                    _svrConnection.ServerInstance = _sqlServerName;
                    _svrConnection.Password = _password;
                    _svrConnection.Login = _userName;
                    
                }
                else
                {
                    _cn.UseIntegratedSecurity = _useIntegratedAuthentication;
                    _cn.ServerName = _sqlServerName;
                    _svrConnection = new ServerConnection(_cn);
                }
                
                _sqlServer = new Server(_svrConnection);
                //_sqlServer.ConnectionContext.MultipleActiveResultSets = true;   //If this is set to true, then an error is generated by the Dependency Walker code. This is a bug see http://connect.microsoft.com/SQLServer/feedback/details/521333/view-dependencies-fails-with-transact-sql-error-515             
                _sqlServer.ConnectionContext.Connect();

                //Need to set the following options so that we don't include the system objects in the db collections
                //as this can have a massive performance hit when we are looping through the collection.
                _sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
                _sqlServer.SetDefaultInitFields(typeof(View), "IsSystemObject");
                _sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
                
                _scripter = new Scripter(_sqlServer);
                                
                return true;
            }
            catch
            {
                throw;
            }
            finally
            {
                //ListOfDatabases = new List<Database>();
            }

        }

        public void DisconnectServer()
        {
            if (_sqlServer != null && _sqlServer.ConnectionContext.IsOpen)
            {
                _sqlServer.ConnectionContext.Disconnect();
                _SelectedDB = null;                
            }

        }

        public Int64 GetBatchRequestsPerSecond()
        {

            Int64 p_int64 = 0;

            if (_sqlServer.ConnectionContext.IsOpen)
            {
                string query = "select cntr_value from sys.dm_os_performance_counters where counter_name = 'Batch Requests/Sec'";
                try
                {
                    p_int64 = (Int64)_sqlServer.ConnectionContext.ExecuteScalar(query);
                    Console.WriteLine("Requests per second = " + p_int64.ToString() + " @ " + DateTime.Now);
                }
                catch (Exception)
                {
                    //Need this empty catch to avoid getting stopped with this error "There is already an open DataReader associated with this Command which must be closed first."
                    //This error occurs when we maybe doing an async call to retrieve data via the ConnectionContext elsewhere.
                    //We can just ignore the error and try again later.
                }
            }

            BatchRequestsPerSecond = p_int64;

            return p_int64;
        }

        #endregion

        #region Public properties

        public static DBServerConnectionSingleton Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (syncLock)
                    {
                        if (_instance == null)
                        {
                            _instance = new DBServerConnectionSingleton();
                        }
                    }
                }
                return _instance;
            }
        }

        public string DBServerName
        {
            get { return _sqlServerName; }
            set
            {
                _sqlServerName = value;
            }
        }
        
        public string Username
        {
            get { return _userName; }
            set
            {
                _userName = value;                
            }
        }
        
        public string Password
        {
            get { return _password; }
            set
            {
                _password = value;                
            }
        }

        public bool UseIntegratedAuthentication
        {
            get { return _useIntegratedAuthentication; }
            set
            {
                _useIntegratedAuthentication = value;
            }
        }


        private int _CountDownTimerValue = 60;
        public int CountDownTimerValue
        {
            get { return _CountDownTimerValue; }
            set
            {
                _CountDownTimerValue = value;                
                NotifyPropertyChanged(MethodBase.GetCurrentMethod().GetPropertyName());
            }

        }

        private Int64 _BatchRequestsPerSecond = 0;
        public Int64 BatchRequestsPerSecond
        {
            get { return _BatchRequestsPerSecond; }
            set
            {
                _BatchRequestsPerSecond = value;
                NotifyPropertyChanged(MethodBase.GetCurrentMethod().GetPropertyName());
            }
        }

        public Server ActiveSQLServerConnection
        {
            get { return _sqlServer; }
        }

        public Scripter DBScripter
        {
            get { return _scripter; }
        }

        private Database _SelectedDB;
        public Database SelectedDB
        {
            get { return _SelectedDB; }

            set
            {
                _SelectedDB = value;
            }
        }

        private NetworkProtocol _selectedNetworkProtocol = NetworkProtocol.TcpIp;
        public NetworkProtocol SelectedNetworkProtocol
        {
            get { return _selectedNetworkProtocol; }
            set
            {
                _selectedNetworkProtocol = value;
            }

        }

        #endregion

        #region NotifyPropertyChanged

        /// <summary>
        /// Raises this object's NotifyPropertyChanged event.
        /// </summary>
        /// <param name="propertyName">The property that has a new value.</param>
        protected void NotifyPropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = this.PropertyChanged;
            if (handler != null)
            {
                var e = new PropertyChangedEventArgs(propertyName);
                handler(this, e);
            }
        }

        #endregion // NotifyPropertyChanged

        #region INotifyPropertyChanged Members

        /// <summary>
        /// Raised when a property on this object has a new value.
        /// </summary>
        public event PropertyChangedEventHandler PropertyChanged;

        #endregion // INotifyPropertyChanged Members
    }//class
}//namespace
